from openpyxl import Workbook;
from openpyxl.styles import Font
import time
import openpyxl
wb = Workbook()
# neo4j
result_local = "./static/file/"
# 转换excel文件
def create_excel(data,word_key):
    wb.create_sheet(title="采集文献数据",index=0)
    # 激活 worksheet
    ws = wb.active
    # 调整单元格样式
    ws.column_dimensions['A'].width = 100
    ws.column_dimensions['E'].width = 100
    ws.column_dimensions['C'].width = 50
    transfer_Chinese = {
        "GB/T":"引用文献",
        "title": "文章标题",
        "author": "作者",
        "inventor": "发明人",
        "abstract": "摘要",
        "announcement_no": "公告号",
        "announcement_data": "公告时间",
        "article_type": "论文类型",
        "year": "年份",
        "key_word": "关键词",
        "qutoCount":"引入量",
        "appid": "申请(专利)号"
    }
    # 生成表头列表
    attr_list = list(data[0].keys())
    append_header = []
    for attr in attr_list:
        if attr != "href":
            append_header.append(transfer_Chinese[attr])
    ws.append(append_header);
    # title_font = Font(name='微软雅黑')
    # 向表格加入数据
    for item in data:
        row = []
        print(42, item)
        for sub_item in item:
            if sub_item != "href":
                if len(item[sub_item]) == 0:
                    item[sub_item] = ''
                row.append(item[sub_item])
        print(45,row)
        ws.append(row)
    # 从第二行加入a标签
    num = 2
    for data_item in data:
        if ("href" in data_item) and ("title" in data_item):
            ws.cell(num, 2).value = '=HYPERLINK("%s","%s")' % (data_item["href"], data_item["title"])
        # 设置自动换行来解决单元格内容覆盖问题
        ws.cell(row=num, column=5, value=data_item["abstract"]).alignment = openpyxl.styles.Alignment(wrapText=True)
        num += 1

    get_time = str(time.time())
    wb.save(result_local + word_key + get_time + ".xlsx")
    print("成功生成excel文件,名称:" + result_local + word_key + get_time + ".xlsx")
    return {
        "status":"success",
        "msg":"数据爬取成功"

    }

